package com.tanhua.common.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.tanhua.common.pojo.UserInfo;
import com.tanhua.common.vo.LoginParamVo;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface UserInfoMapper extends BaseMapper<UserInfo> {
    /**
     * 查询行业前十
     * @return
     */
    @Select("SELECT industry AS title,COUNT(*) AS amount FROM tb_user_info GROUP BY industry ORDER BY amount DESC LIMIT 0,10")
    List<LoginParamVo> getIndustry();

    /**
     * 统计查询性别
     * @return
     */
    @Select("SELECT\n" +
            "CASE\n" +
            "\t\t\n" +
            "\tWHEN\n" +
            "\t\t( sex = 1 ) THEN\n" +
            "\t\t\t'男' \n" +
            "\t\t\tWHEN ( sex = 2 ) THEN\n" +
            "\t\t\t'女' ELSE '未知' \n" +
            "\t\tEND 'title',\n" +
            "\tcount(*) amount \n" +
            "FROM\n" +
            "\ttb_user_info \n" +
            "GROUP BY\n" +
            "CASE\n" +
            "\t\t\n" +
            "\t\tWHEN ( sex = 1 ) THEN\n" +
            "\t\t'男' \n" +
            "\t\tWHEN ( sex = 2 ) THEN\n" +
            "\t\t'女' ELSE '未知' \n" +
            "\tEND\n" +
            "\tORDER BY amount\n" +
            "\tASC")
    List<LoginParamVo> getSexP();

    /**
     * 统计分段年龄查询
     * @return
     */
    @Select("SELECT\n" +
            "CASE\n" +
            "\tWHEN\n" +
            "\t\t( age < 20 ) THEN\n" +
            "\t\t\t'19岁以下' \n" +
            "\t\t\tWHEN ( age >= 20 AND age < 30 ) THEN\n" +
            "\t\t\t'20-29岁' \n" +
            "\t\t\tWHEN ( age >= 30 AND age < 40 ) THEN\n" +
            "\t\t\t'30-39岁' \n" +
            "\t\t\tWHEN ( age >= 40 AND age < 50 ) THEN\n" +
            "\t\t\t'40-49岁' ELSE '50岁以上' \n" +
            "\t\tEND 'title',\n" +
            "\tcount(*) amount \n" +
            "FROM\n" +
            "\ttb_user_info \n" +
            "GROUP BY\n" +
            "CASE\n" +
            "\t\tWHEN ( age < 20 ) THEN\n" +
            "\t\t'19岁以下' \n" +
            "\t\tWHEN ( age >= 20 AND age < 30 ) THEN\n" +
            "\t\t'20-29岁' \n" +
            "\t\tWHEN ( age >= 30 AND age < 40 ) THEN\n" +
            "\t\t'30-39岁' \n" +
            "\t\tWHEN ( age >= 40 AND age < 50 ) THEN\n" +
            "\t\t'40-49岁' ELSE '50岁以上' \n" +
            "\tEND \n" +
            "ORDER BY\n" +
            "\t1")
    List<LoginParamVo> getAge();

    /**
     * 城市统计查询
     * @return
     */
    @Select("SELECT LEFT(city,2) AS title, COUNT(*) AS amount\n" +
            "FROM tb_user_info\n" +
            "GROUP BY title")
    List<LoginParamVo> getCity();

    @Select("SELECT SUBSTRING_INDEX(city,\"-\",1) AS title, COUNT(*) AS amount\n" +
            "FROM tb_user_info\n" +
            "GROUP BY title")
    List<LoginParamVo> getCityArea();

}